/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Role;
import model.Shistory;
import model.Staff;

/**
 *
 * @author Thai Ha Cong
 */
public class StaffAuthenticationDAO {

    private Connection con;

    public StaffAuthenticationDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public boolean checkLogin(Staff staff) {
        boolean result = false;

        String sqlst = "SELECT * FROM staff "
                + "WHERE loginName = ? AND password = ?";
        try {
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, staff.getLoginName());
            ps.setString(2, staff.getPassword());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                result = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public Staff getStaffFromLoginName(String loginName) {
        Staff result = new Staff();

        try {
            String sqlst = "SELECT * FROM staff "
                    + "WHERE staff.loginName = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, loginName);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
//                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstName = rs.getString("firstName");
                String lastName = rs.getString("lastName");
                String address = rs.getString("address");
                String role = rs.getString("role");
                Date dob = (Date) rs.getDate("dob");
                Date openTime = rs.getTimestamp("openTime");
                Date closeTime = rs.getTimestamp("closeTime");

                result = new Staff(idStaff,role, email, loginName, password, firstName, lastName, address, dob, openTime, closeTime);
            }

            //role list
            List<Role> roleList = new ArrayList<Role>();
            
            String sqlst2 = "SELECT role.* FROM staff, role, staffrole "
                    + "WHERE staff.loginName = ? "
                    + "AND staff.idStaff = staffrole.idStaff "
                    + "AND staffrole.idRole = role.idRole";
            PreparedStatement ps2 = con.prepareStatement(sqlst2);
            ps2.setString(1, loginName);

            ResultSet rs2 = ps2.executeQuery();
            
            while (rs2.next()) {
                Integer idRole = rs2.getInt(1);
                String name = rs2.getString(2);
                
                Role temp = new Role(idRole, name);
                roleList.add(temp);
            }
            
            result.setRoleList(roleList);
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public boolean addAction(String actionName, Integer idStaff) {
        boolean result = false;
        
        String sqlst = "INSERT INTO shistory(actionName, actionTime, staffId) "
                    + "VALUES (?, NOW(), ?)";
        try {
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, actionName);
            ps.setInt(2, idStaff);
            
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(StaffAuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
    
    public List<Shistory> getAllShistory()
    {
        List<Shistory> result = new ArrayList<Shistory>();
        String sqlst = "SELECT * FROM shistory ORDER BY actionTime DESC LIMIT 30";

        try {
            PreparedStatement ps = con.prepareStatement(sqlst); 

            ResultSet rs = ps.executeQuery();
            //System.out.println("Last"+rs.last());
            while (rs.next()) {
                Integer idStaff = rs.getInt("staffId");
                String actionName = rs.getString("actionName");
                Date actionTime = rs.getDate("actionTime");
               
                Staff staff = new StaffDAO().getStaffByStaffId(idStaff);
                //System.out.println(staff);
                Shistory history = new Shistory(staff, actionTime, actionName);
                
                result.add(history);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
       

        return result;
    }
    
    public List<Shistory> SearchShistory(String keyword)
    {
        List<Shistory> result = new ArrayList<Shistory>();
        String sqlst = "SELECT * FROM shistory,staff Where shistory.actionName LIKE ? "
                + "or staff.loginName LIKE ? or staff.firstname LIKE ? or staff.lastname LIKE ? "
                + "and shistory.staffId = staff.idStaff ORDER BY actionTime DESC LIMIT 30";

        try {
            PreparedStatement ps = con.prepareStatement(sqlst); 
            ps.setString(1, keyword);
            ps.setString(2, keyword);
            ps.setString(3, keyword);
             ps.setString(4, keyword);
            ResultSet rs = ps.executeQuery();
            //System.out.println("Last"+rs.last());
            while (rs.next()) {
                Integer idStaff = rs.getInt("staffId");
                String actionName = rs.getString("actionName");
                Date actionTime = rs.getDate("actionTime");
               
                Staff staff = new StaffDAO().getStaffByStaffId(idStaff);
                System.out.println(staff);
                Shistory history = new Shistory(staff, actionTime, actionName);
                
                result.add(history);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public List<Staff> getStaffFromRole(String Staffrole) {
        List <Staff> result = new ArrayList<Staff>();

        try {
            String sqlst = "SELECT * FROM staff "
                    + "WHERE staff.role = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, Staffrole);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
                String loginName = rs.getString("loginName");
//                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstName = rs.getString("firstName");
                String lastName = rs.getString("lastName");
                String address = rs.getString("address");
                String role = rs.getString("role");
                Date dob = (Date) rs.getDate("dob");
                Date openTime = rs.getTimestamp("openTime");
                Date closeTime = rs.getTimestamp("closeTime");

                Staff staff = new Staff(idStaff,role, email, loginName, password, firstName, lastName, address, dob, openTime, closeTime);
                result.add(staff);
            } 
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
}
